USE StudentManagementIntelligent
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Longdt, Anhntv>
-- Create date: <16/11>
-- Description:	<Assigment CWF1>
-- =============================================

-- 16/11

CREATE PROCEDURE sp_GetTeacherLogin
@username nvarchar(100),
@password nvarchar(100)
AS
BEGIN
	SELECT *FROM Teacher WHERE TeacherID = @username AND [Password] = @password
END
GO

CREATE PROCEDURE sp_GetAllTeacher
AS
BEGIN
	SELECT *FROM Teacher
END
GO
CREATE PROCEDURE sp_GetAllManager
AS
BEGIN
	SELECT *FROM Manager
END
GO
--17/11
CREATE PROCEDURE sp_GetManagerLogin
	@username nvarchar(100),
	@password nvarchar(100)
AS
BEGIN
	SELECT * FROM Manager WHERE ManagerID = @username AND [Password] = @password
END
GO
-- 18/11
CREATE PROCEDURE sp_GetStudentInClass
@classID nvarchar(100)
AS
BEGIN
SELECT a.StudentID, b.Name
FROM ClassDetail AS a INNER JOIN Student AS b
ON a.StudentID = b.StudentID
WHERE a.ClassID = @classID
GROUP BY a.StudentID,b.Name
END
GO

--21/11
CREATE PROCEDURE sp_GetAllClassID
AS
BEGIN
	SELECT ClassID FROM Class
END
GO

--22/11
CREATE PROCEDURE sp_GetAllClassIdByTeacher
@teacherID  nvarchar(100)
AS
BEGIN
	SELECT ClassID 
	FROM Class
	WHERE Class.TeacherID = @teacherID
END
GO

--GO
--SELECT *FROM Class
--SELECT *FROM ClassDetail
--SELECT *FROM ClassHistory
--SELECT *FROM Teacher
--SELECT *FROM Attendace

--25/11
GO
CREATE PROCEDURE sp_InsertTeacherAvatar
@teacherID nvarchar(100),
@image image
AS
BEGIN
	INSERT INTO TeacherAvatar VALUES(@teacherID,@image)
END
GO
CREATE PROCEDURE sp_GetTeacherAvatar
@teacherID nvarchar(100)
AS
BEGIN
	SELECT *FROM TeacherAvatar WHERE TeacherID = @teacherID
END
GO

--CREATE PROCEDURE sp_GetAllTeacher
--AS
--BEGIN
--SELECT *FROM Teacher
--END
--GO
-- 26/11
CREATE PROCEDURE sp_DeleteTeacher
@teacherID nvarchar(100)
AS
BEGIN
	DELETE FROM Teacher WHERE Teacher.TeacherID = @teacherID
END
GO
CREATE PROCEDURE sp_InsertTeacher
@teacherID nvarchar(100),
@password nvarchar(100),
@name nvarchar(100),
@gender nvarchar(100), 
@dob smalldatetime ,
@address nvarchar(100),
@email nvarchar(100),
@phone nvarchar(100),
@description nvarchar(100)
AS
BEGIN
	INSERT INTO Teacher VALUES(@teacherID,@password,@name,@gender,@dob,@address,@email,@phone,@description)
END
GO
CREATE PROCEDURE sp_UpdateTeacher
@teacherID nvarchar(100),
@password nvarchar(100),
@name nvarchar(100),
@gender nvarchar(100), 
@dob smalldatetime ,
@address nvarchar(100),
@email nvarchar(100),
@phone nvarchar(100),
@description nvarchar(100)
AS
BEGIN
	UPDATE Teacher SET 
	[Password] = @password,
	Name = @name,
	Gender = @gender,
	DOB = @dob,
	[Address]=@address,
	Email = @email,
	Phone = @phone,
	[Description] =@description
	WHERE TeacherID = @teacherID
END
GO

--CREATE PROCEDURE sp_GetAllSubjectNameInSem
--@semester nvarchar(100)
--AS
--BEGIN
--SELECT a.SubjectName
--FROM [Subject] AS a INNER JOIN Sem AS b
--ON a.SubjectID = b.SubjectID
--WHERE b.Semester = @semester
--END
--GO
--EXEC sp_GetAllSubjectNameInSem 'SEM 3'

CREATE PROC sp_GetSubject
	@sem nvarchar(100)
AS
BEGIN
	SELECT a.SubjectName FROM [Subject] AS a WHERE a.Sem = @sem
END
go

Create Proc sp_GetAllSem
as
begin
	select Sem from [Subject]
	group by Sem
end
go

-- 27/11
CREATE PROCEDURE sp_GetTeacherIDAvatar
@teacherID nvarchar(100)
AS
BEGIN
SELECT TeacherID FROM TeacherAvatar WHERE TeacherAvatar.TeacherID = @teacherID
END
GO
CREATE PROCEDURE sp_UpdateTeacherAvatar
@teacherID nvarchar(100),
@avatar image
AS
BEGIN
UPDATE TeacherAvatar SET Avatar = @avatar
WHERE TeacherAvatar.TeacherID = @teacherID
END
GO
CREATE PROCEDURE sp_DeleteTeacherAvatar
@teacherID nvarchar(100)
AS
BEGIN
DELETE FROM TeacherAvatar WHERE TeacherAvatar.TeacherID = @teacherID
END
GO
CREATE PROCEDURE sp_GetAllClass
AS
BEGIN
	SELECT *FROM Class
END
GO
-- 28/11
CREATE PROCEDURE sp_GetClassHistoryByTeacherID
@teacherID nvarchar(100)
AS
BEGIN
SELECT a.ClassID, a.TeachTimeStart AS 'Start',
--CASE WHEN a.TeachTimeEnd IS NULL THEN b.TimeEnd
--ELSE 
a.TeachTimeEnd /*END*/ AS 'End'
FROM ClassHistory AS a INNER JOIN Class AS b
ON a.ClassID = b.ClassID
WHERE a.TeacherID = @teacherID
GROUP BY a.ClassID,a.TeachTimeStart,a.TeachTimeEnd,b.ClassID,b.TimeEnd
ORDER BY Start DESC
END
GO
CREATE PROCEDURE sp_SearchTeacherByName
@teacherName nvarchar(100)
AS
BEGIN
SELECT *FROM Teacher WHERE Name LIKE '%'+@teacherName+'%'
END
GO
CREATE PROCEDURE sp_GetClassInformation
AS
BEGIN
SELECT a.ClassID , c.Name AS 'Teacher Name', a.TimeSlot,a.Room, COUNT(b.StudentID) AS 'Total Student' 
FROM Class AS a LEFT JOIN ClassDetail AS b
ON a.ClassID = b.ClassID LEFT JOIN Teacher AS c ON a.TeacherID = c.TeacherID
WHERE a.TimeEnd > GETDATE()
GROUP BY a.ClassID, c.Name, a.TimeSlot, a.Room
END
GO
CREATE PROCEDURE sp_GetClassIDLearning
AS
BEGIN
SELECT ClassID
FROM Class
WHERE TimeStart < GETDATE() AND TimeEnd > GETDATE()
END
GO
CREATE PROCEDURE sp_GetClassIDWaiting
AS
BEGIN
SELECT ClassID
FROM Class
WHERE TimeStart > GETDATE()
END
GO
CREATE PROCEDURE sp_GetClassIDFinished
AS
BEGIN
SELECT ClassID
FROM Class
WHERE TimeEnd < GETDATE()
END
GO
CREATE PROCEDURE sp_GetClassHistoryByClassID
@classID nvarchar(100)
AS
BEGIN
	SELECT a.TeacherID, b.Name,a.TeachTimeStart AS 'Start', a.TeachTimeEnd AS 'End'
	FROM ClassHistory AS a INNER JOIN Teacher AS b
	ON a.TeacherID = b.TeacherID
	WHERE a.ClassID = @classID
	GROUP BY a.TeacherID,a.TeachTimeStart,a.TeachTimeEnd,b.Name	
	ORDER BY [Start] DESC
END
GO
CREATE PROCEDURE sp_GetClassTimeStartAndTimeEnd
@classID nvarchar(100)
AS
BEGIN
	SELECT ClassID, TimeStart, TimeEnd FROM Class WHERE ClassID = @classID
END
GO
CREATE PROCEDURE sp_GetClassDetailStudent
@classID nvarchar(100)
AS
BEGIN
SELECT a.StudentID,b.Name,a.[Status]
FROM ClassDetail AS a LEFT JOIN Student AS b
ON a.StudentID = b.StudentID
WHERE a.ClassID=@classID
GROUP BY a.StudentID,a.Status,a.ClassID,b.Name
END
GO
CREATE PROCEDURE sp_GetClassByClassID
@classID nvarchar(100)
AS
BEGIN
SElECT *FROM Class WHERE ClassID = @classID
END
GO
CREATE PROCEDURE sp_GetAllTeacherID
AS
BEGIN
SELECT TeacherID FROM Teacher
END
GO
CREATE PROC sp_GetAllStudent
AS
BEGIN
	select * from Student
END
GO


CREATE PROC sp_GetTotalSessionBySubjecName
	@subjectName nvarchar(100)
AS
BEGIN
	SELECT SubjectID,SubjectName,TotalSession
	FROM [Subject]
	Where SubjectName = @subjectName
END
GO
---- done.....
CREATE PROC sp_InsertAttendance
	@attendanceDetailID nvarchar(100),
	@attendanceID nvarchar(100),
	@subjectID nvarchar(100),
	@classID nvarchar(100),	
	@studentID nvarchar(100),
	@timeAttendance smalldatetime,
	@sessionCount int,
	@status nvarchar(100)
AS
BEGIN
	INSERT INTO Attendance(AttendanceID,ClassID,TimeAttendance)
	VALUES(@attendanceID,@classID,@timeAttendance)
	INSERT INTO AttendanceDetail(AttendanceDetailID,AttendanceID,StudentID,SubjectID,[Status],SessionCount)
	VALUES(@attendanceDetailID,@attendanceID,@studentID,@subjectID,@status,@sessionCount)
	
END
GO

--select AttendanceDetailID,ClassID,SubjectID,StudentID,[Status]
--from AttendanceDetail ad
--join Attendance a
--on ad.AttendanceDetailID = ad.AttendanceDetailID
--where a.AttendanceID = 'apj2-1'
--GO
CREATE PROC sp_GetAttendanceByID
	@classID nvarchar(100),
	@attendanceID nvarchar(100)
	
AS
BEGIN
	SELECT ad.StudentID,s.Name,ad.[Status]
	FROM AttendanceDetail ad
	INNER JOIN Attendance a
	ON ad.AttendanceID = a.AttendanceID
	INNER JOIN Student s
	ON ad.StudentID = s.StudentID
	WHERE ad.AttendanceID = @attendanceID AND a.ClassID = @classID
	
END
GO

-------------------------
CREATE PROCEDURE sp_InsertClass
@classID nvarchar(100),
@teacherID nvarchar(100),
@className nvarchar(100),
@timeStart smalldatetime,
@timeEnd smalldatetime,
@timeSlot nvarchar(100),
@room nvarchar(100)
AS
BEGIN
INSERT INTO Class VALUES(@classID,@teacherID,@className,@timeStart,@timeEnd,@timeSlot,@room)
END
GO
CREATE PROCEDURE sp_UpdateClass
@classID nvarchar(100),
@className nvarchar(100),
@timeStart smalldatetime,
@timeEnd smalldatetime,
@timeSlot nvarchar(100),
@room nvarchar(100)
AS
BEGIN
UPDATE Class SET
ClassName = @className,
TimeStart = @timeStart,
TimeEnd = @timeEnd,
TimeSlot = @timeSlot,
Room = @room	
WHERE ClassID = @classID
END
GO
-- Update thong tin Class de hien thi giao vien hien thoi
CREATE PROCEDURE sp_UpdateClassNewTeacher
@newTeacherID nvarchar(100),
@classID nvarchar(100),
@timeLine smalldatetime
AS
BEGIN
UPDATE Class SET TeacherID = @newTeacherID 
WHERE ClassID = @classID AND @timeLine < TimeEnd AND @timeLine > TimeStart
END
GO
-- Update thong tin ClassHistort de set TeachTimeEnd
CREATE PROCEDURE sp_UpdateClassHistoryNewTeacher
@classID nvarchar(100),
@currentTeacherID nvarchar(100),
@timeLine smalldatetime
AS
BEGIN
UPDATE ClassHistory SET TeachTimeEnd = @timeLine 
WHERE ClassID = @classID AND TeacherID = @currentTeacherID AND TeachTimeEnd IS NULL
END
GO
-- Insert record moi vao bang ClassHistory
CREATE PROCEDURE sp_InsertClassHistoryNewTeacher
@classID nvarchar(100),
@newTeacherID nvarchar(100),
@timeLine smalldatetime
AS
BEGIN
INSERT INTO ClassHistory(ClassID,TeacherID,TeachTimeStart)VALUES (@classID,@newTeacherID,@timeLine)
END
GO
-- done change 
CREATE PROCEDURE sp_InsertClassHistory
@classID nvarchar(100),
@teacherID nvarchar(100),
@timeStart smalldatetime
AS
BEGIN
INSERT INTO ClassHistory(ClassID,TeacherID,TeachTimeStart) VALUES (@classID,@teacherID,@timeStart)
END
GO
CREATE PROCEDURE sp_SearchClassByClassID
@classID nvarchar(100)
AS
BEGIN
SELECT a.ClassID , c.Name AS 'Teacher Name', a.TimeSlot,a.Room, COUNT(b.StudentID) AS 'Total Student' 
FROM Class AS a LEFT JOIN ClassDetail AS b
ON a.ClassID = b.ClassID LEFT JOIN Teacher AS c ON a.TeacherID = c.TeacherID
WHERE a.ClassID LIKE '%'+@classID+'%'
GROUP BY a.ClassID, c.Name, a.TimeSlot, a.Room
END
GO

CREATE PROCEDURE sp_GetStudentInFormation
@studentID nvarchar(100)
AS
BEGIN
SELECT *FROM Student AS a WHERE a.StudentID = @studentID
END
GO

CREATE PROCEDURE sp_GetAllStudentByStatus
@status nvarchar(100)
AS
BEGIN
SELECT a.StudentID,a.Name,a.Status
FROM Student AS a
WHERE a.Status =@status
END
GO

CREATE PROCEDURE sp_GetAllStudentInDB
AS
BEGIN
SELECT a.StudentID,a.Name,a.Status
FROM Student AS a
END
GO

CREATE PROCEDURE sp_InsertStudent
@studentID nvarchar(100),
@name nvarchar(100),
@gender nvarchar(100),
@dob smalldatetime,
@address nvarchar(100),
@email nvarchar(100),
@phone nvarchar(100)
AS
BEGIN
INSERT INTO Student(StudentID,Name,Gender,DOB,[Address],Email,Phone) 
VALUES(@studentID,@name,@gender,@dob,@address,@email,@phone)
END
GO
CREATE PROCEDURE sp_UpdateStudent
@studentID nvarchar(100),
@name nvarchar(100),
@gender nvarchar(100),
@dob smalldatetime,
@address nvarchar(100),
@email nvarchar(100),
@phone nvarchar(100)
AS
BEGIN
UPDATE Student SET
Name = @name,
Gender = @gender,
DOB = @dob,
[Address] = @address,
Email =@email,
Phone = @phone
WHERE StudentID = @studentID
END
GO
CREATE PROCEDURE sp_DeleteStudent
@studentID nvarchar(100)
AS
BEGIN
DELETE FROM Student WHERE Student.StudentID = @studentID
END
GO
CREATE PROCEDURE sp_SearchStudent
@name nvarchar(100)
AS
BEGIN
SELECT a.StudentID, a.Name, a.[Status]
FROM Student AS a
WHERE a.Name LIKE '%'+@name+'%'
END
GO
CREATE PROCEDURE sp_TotalPresentAttendance
@studentID nvarchar(100)
AS
BEGIN
SELECT a.StudentID, a.Name, c.SubjectName, COUNT(b.[Status]) AS 'Present', COUNT(b.SessionCount) AS 'Attendace' ,c.TotalSession AS 'Total Session'
FROM Student AS a INNER JOIN (AttendanceDetail AS b INNER JOIN [Subject] AS c ON b.SubjectID = c.SubjectID)
ON a.StudentID = b.StudentID 
WHERE a.StudentID=@studentID AND b.[Status] ='Present'
GROUP BY a.StudentID,a.Name,b.[Status], c.TotalSession,c.SubjectName
END
GO

CREATE PROCEDURE sp_GetAttedanceDetail
@studentID nvarchar(100),
@subjectID nvarchar(100)
AS
BEGIN
SELECT StudentID, SubjectID, [Status], SessionCount FROM AttendanceDetail 
WHERE StudentID=@studentID AND SubjectID =@subjectID
END
GO

SELECT *FROM Attendance
SELECT *FROM AttendanceDetail
GO

